/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */

/*
 * Search.java
 *
 * Created on Dec 20, 2008, 4:54:52 PM
 */

package GUI;

import Dictionary.Dictionary;
import Interfaces.Loadable;
import functions.Dogy;
import functions.Reports;
import java.awt.Color;
import java.awt.event.ActionEvent;
import java.awt.event.KeyEvent;
import java.io.File;
import java.io.IOException;
import java.sql.Date;
import java.sql.SQLException;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.Collection;
import java.util.Collections;
import java.util.Iterator;
import java.util.Random;
import java.util.TreeMap;
import java.util.logging.Level;
import java.util.logging.Logger;
import javax.swing.AbstractAction;
import javax.swing.ActionMap;
import javax.swing.InputMap;
import javax.swing.JFileChooser;
import javax.swing.KeyStroke;
import javax.swing.filechooser.FileFilter;
import javax.swing.table.DefaultTableModel;
import jxl.*;
import jxl.format.Border;
import jxl.format.BorderLineStyle;
import jxl.format.Colour;
import jxl.format.PageOrientation;
import jxl.write.Formula;
import jxl.write.Label;
import jxl.write.Number;
import jxl.write.WritableCellFormat;
import jxl.write.WritableFont;
import jxl.write.WritableSheet;
import jxl.write.WritableWorkbook;
import model.Database;
import model.ReportTypes.DiagnoseReport;
import model.ReportTypes.VakcineReport;
import model.ReportTypes.VisitOverallReport;
import model.ReportTypes.ProfitReport;
import model.ReportTypes.VisitReport;
import model.User;
import stuff.TableTextAreaRenderer;


/**
 *
 * @author GiK
 */
public class Otchet extends javax.swing.JPanel {
    // @Exception handler
    private MainFrame frame;
    private String dateText;
    private Date dateFrom=null, dateTo=null;
    private boolean toExcel, toPrint;

    private static int VISITS=2,
                       DIAGNOSES=3;
    /** Creates new form Search */
    public Otchet(MainFrame mainFr) {
        initComponents();
        frame = mainFr;
        fileChooser = new JFileChooser();
        
        SimpleDateFormat sdf = new SimpleDateFormat("dd.MM.yyyy");
        dateText = sdf.format(Calendar.getInstance().getTime());
        
            fromDate.setText(Dictionary.DD_MM_YY);
            fromDate.setForeground(Color.gray);
            toDate.setText(Dictionary.DD_MM_YY);
            toDate.setForeground(Color.gray);

            toExcel = false;
            toPrint = false;
            KeyStroke ctrlW = KeyStroke.getKeyStroke(KeyEvent.VK_X,2);
        InputMap map = visitReportTable.getInputMap(javax.swing.JTable.WHEN_FOCUSED);
        map.put(ctrlW, "ctrlX");
        visitReportTable.getInputMap(javax.swing.JTable.WHEN_IN_FOCUSED_WINDOW).put(ctrlW, "ctrlX");
        ActionMap actionMap = visitReportTable.getActionMap();
        actionMap.put("ctrlX", new AbstractAction() {
                     public void actionPerformed(ActionEvent actionEvent)
                     {
                         drugLimitField.setText("");
                         visitVariance.setText("");
                         limitDialog.pack();
                         limitDialog.setVisible(true);
                         drugLimitField.requestFocus();
                         Dogy.placeToCenter(limitDialog);
                     }
             });
    }

    private VisitOverallReport fillVisitOverall(ArrayList<ProfitReport> visRep) {
        double drugsTotalBuy=0,
                drugsTotalSell=0,
                drugsProfit=0,
                operTotalSell=0,
                operProfit=0,
                visitTotalPrice=0,
                visitOtherTotalPrice=0,
                totalBuy=0,
                totalSell=0,
                totalProfit=0;
        VisitOverallReport overRep = new VisitOverallReport();
        for(int i=0; i< visRep.size(); i++)
        {
            drugsTotalSell += visRep.get(i).getDrugsPrice();
            drugsTotalBuy += visRep.get(i).getDrugsBuyPrice();
            drugsProfit += visRep.get(i).getDrugsProfit();
            operTotalSell += visRep.get(i).getOperPrice();
            operProfit +=visRep.get(i).getOperPrice();
            visitTotalPrice +=visRep.get(i).getVisitPrice();
            visitOtherTotalPrice += visRep.get(i).getOtherPrice();
        }
        overRep.setDrugsTotalBuy(drugsTotalBuy);
        overRep.setDrugsTotalSell(drugsTotalSell);
        overRep.setOperTotalSell(operTotalSell);
        overRep.setVisitOtherTotalPrice(visitOtherTotalPrice);
        overRep.setVisitTotalPrice(visitTotalPrice);

        return overRep;
    }

    /** This method is called from within the constructor to
     * initialize the form.
     * WARNING: Do NOT modify this code. The content of this method is
     * always regenerated by the Form Editor.
     */
    @SuppressWarnings("unchecked")
    // <editor-fold defaultstate="collapsed" desc="Generated Code">//GEN-BEGIN:initComponents
    private void initComponents() {

        limitDialog = new javax.swing.JDialog();
        visitSettingsPanel = new javax.swing.JPanel();
        jLabel2 = new javax.swing.JLabel();
        jSeparator1 = new javax.swing.JSeparator();
        jLabel3 = new javax.swing.JLabel();
        jLabel4 = new javax.swing.JLabel();
        jButton1 = new javax.swing.JButton();
        jButton3 = new javax.swing.JButton();
        drugLimitField = new javax.swing.JFormattedTextField();
        visitVariance = new javax.swing.JFormattedTextField();
        visitLimitField = new javax.swing.JTextField();
        jLabel5 = new javax.swing.JLabel();
        jLabel6 = new javax.swing.JLabel();
        totalLimitLabel = new javax.swing.JLabel();
        fileChooser = new javax.swing.JFileChooser();
        saveAsButton = new javax.swing.JButton();
        fromDate = new javax.swing.JFormattedTextField();
        jLabel1 = new javax.swing.JLabel();
        toDate = new javax.swing.JFormattedTextField();
        showReport = new javax.swing.JButton();
        jCheckBox1 = new javax.swing.JCheckBox();
        jButton2 = new javax.swing.JButton();
        jPanel5 = new javax.swing.JPanel();
        reports = new javax.swing.JTabbedPane();
        jPanel1 = new javax.swing.JPanel();
        jScrollPane2 = new javax.swing.JScrollPane();
        vakcTable = new javax.swing.JTable();
        jPanel2 = new javax.swing.JPanel();
        visitSplitter = new javax.swing.JSplitPane();
        jScrollPane3 = new javax.swing.JScrollPane();
        profitReportTable = new javax.swing.JTable();
        jScrollPane1 = new javax.swing.JScrollPane();
        profitOverallTable = new javax.swing.JTable();
        jPanel3 = new javax.swing.JPanel();
        jScrollPane4 = new javax.swing.JScrollPane();
        visitReportTable = new javax.swing.JTable();
        jPanel4 = new javax.swing.JPanel();
        jScrollPane6 = new javax.swing.JScrollPane();
        diagTable = new javax.swing.JTable();
        withPredv = new javax.swing.JCheckBox();

        jLabel2.setText("Настройки");

        jLabel3.setText("Лекарства");

        jLabel4.setText("±");

        jButton1.setFont(new java.awt.Font("Tahoma", 0, 12)); // NOI18N
        jButton1.setText("Подтвердить");
        jButton1.addActionListener(new java.awt.event.ActionListener() {
            public void actionPerformed(java.awt.event.ActionEvent evt) {
                jButton1ActionPerformed(evt);
            }
        });

        jButton3.setFont(new java.awt.Font("Tahoma", 0, 12)); // NOI18N
        jButton3.setText("Закрыть");
        jButton3.addActionListener(new java.awt.event.ActionListener() {
            public void actionPerformed(java.awt.event.ActionEvent evt) {
                jButton3ActionPerformed(evt);
            }
        });

        drugLimitField.setFormatterFactory(new javax.swing.text.DefaultFormatterFactory(new javax.swing.text.NumberFormatter(new java.text.DecimalFormat("###0.##"))));
        drugLimitField.addFocusListener(new java.awt.event.FocusAdapter() {
            public void focusLost(java.awt.event.FocusEvent evt) {
                drugLimitFieldFocusLost(evt);
            }
        });
        drugLimitField.addKeyListener(new java.awt.event.KeyAdapter() {
            public void keyReleased(java.awt.event.KeyEvent evt) {
                drugLimitFieldKeyReleased(evt);
            }
        });

        visitVariance.setFormatterFactory(new javax.swing.text.DefaultFormatterFactory(new javax.swing.text.NumberFormatter(new java.text.DecimalFormat("###0.##"))));
        visitVariance.addFocusListener(new java.awt.event.FocusAdapter() {
            public void focusLost(java.awt.event.FocusEvent evt) {
                visitVarianceFocusLost(evt);
            }
        });

        visitLimitField.addKeyListener(new java.awt.event.KeyAdapter() {
            public void keyReleased(java.awt.event.KeyEvent evt) {
                visitLimitFieldKeyReleased(evt);
            }
        });

        jLabel5.setText("Визиты");

        jLabel6.setText("Итого:");

        totalLimitLabel.setFont(new java.awt.Font("Tahoma", 1, 12)); // NOI18N
        totalLimitLabel.setText("0");

        javax.swing.GroupLayout visitSettingsPanelLayout = new javax.swing.GroupLayout(visitSettingsPanel);
        visitSettingsPanel.setLayout(visitSettingsPanelLayout);
        visitSettingsPanelLayout.setHorizontalGroup(
            visitSettingsPanelLayout.createParallelGroup(javax.swing.GroupLayout.Alignment.LEADING)
            .addGroup(visitSettingsPanelLayout.createSequentialGroup()
                .addContainerGap()
                .addGroup(visitSettingsPanelLayout.createParallelGroup(javax.swing.GroupLayout.Alignment.LEADING)
                    .addComponent(jLabel2, javax.swing.GroupLayout.PREFERRED_SIZE, 188, javax.swing.GroupLayout.PREFERRED_SIZE)
                    .addComponent(jSeparator1, javax.swing.GroupLayout.DEFAULT_SIZE, 261, Short.MAX_VALUE)
                    .addGroup(visitSettingsPanelLayout.createSequentialGroup()
                        .addGroup(visitSettingsPanelLayout.createParallelGroup(javax.swing.GroupLayout.Alignment.TRAILING, false)
                            .addComponent(jButton3, javax.swing.GroupLayout.Alignment.LEADING, javax.swing.GroupLayout.DEFAULT_SIZE, javax.swing.GroupLayout.DEFAULT_SIZE, Short.MAX_VALUE)
                            .addComponent(jLabel5, javax.swing.GroupLayout.Alignment.LEADING, javax.swing.GroupLayout.DEFAULT_SIZE, javax.swing.GroupLayout.DEFAULT_SIZE, Short.MAX_VALUE)
                            .addComponent(jLabel3, javax.swing.GroupLayout.Alignment.LEADING, javax.swing.GroupLayout.DEFAULT_SIZE, 83, Short.MAX_VALUE)
                            .addComponent(jLabel6, javax.swing.GroupLayout.Alignment.LEADING, javax.swing.GroupLayout.DEFAULT_SIZE, javax.swing.GroupLayout.DEFAULT_SIZE, Short.MAX_VALUE))
                        .addGroup(visitSettingsPanelLayout.createParallelGroup(javax.swing.GroupLayout.Alignment.LEADING)
                            .addGroup(visitSettingsPanelLayout.createSequentialGroup()
                                .addPreferredGap(javax.swing.LayoutStyle.ComponentPlacement.RELATED)
                                .addGroup(visitSettingsPanelLayout.createParallelGroup(javax.swing.GroupLayout.Alignment.LEADING, false)
                                    .addComponent(totalLimitLabel, javax.swing.GroupLayout.DEFAULT_SIZE, javax.swing.GroupLayout.DEFAULT_SIZE, Short.MAX_VALUE)
                                    .addGroup(visitSettingsPanelLayout.createSequentialGroup()
                                        .addComponent(drugLimitField, javax.swing.GroupLayout.PREFERRED_SIZE, 107, javax.swing.GroupLayout.PREFERRED_SIZE)
                                        .addPreferredGap(javax.swing.LayoutStyle.ComponentPlacement.RELATED)
                                        .addComponent(jLabel4)
                                        .addPreferredGap(javax.swing.LayoutStyle.ComponentPlacement.UNRELATED)
                                        .addComponent(visitVariance, javax.swing.GroupLayout.PREFERRED_SIZE, 46, javax.swing.GroupLayout.PREFERRED_SIZE))
                                    .addComponent(visitLimitField)))
                            .addGroup(visitSettingsPanelLayout.createSequentialGroup()
                                .addGap(73, 73, 73)
                                .addComponent(jButton1, javax.swing.GroupLayout.DEFAULT_SIZE, javax.swing.GroupLayout.DEFAULT_SIZE, Short.MAX_VALUE)))))
                .addContainerGap(javax.swing.GroupLayout.DEFAULT_SIZE, Short.MAX_VALUE))
        );
        visitSettingsPanelLayout.setVerticalGroup(
            visitSettingsPanelLayout.createParallelGroup(javax.swing.GroupLayout.Alignment.LEADING)
            .addGroup(visitSettingsPanelLayout.createSequentialGroup()
                .addComponent(jLabel2)
                .addPreferredGap(javax.swing.LayoutStyle.ComponentPlacement.RELATED)
                .addComponent(jSeparator1, javax.swing.GroupLayout.PREFERRED_SIZE, 10, javax.swing.GroupLayout.PREFERRED_SIZE)
                .addPreferredGap(javax.swing.LayoutStyle.ComponentPlacement.RELATED, javax.swing.GroupLayout.DEFAULT_SIZE, Short.MAX_VALUE)
                .addGroup(visitSettingsPanelLayout.createParallelGroup(javax.swing.GroupLayout.Alignment.BASELINE)
                    .addComponent(jLabel3)
                    .addComponent(drugLimitField, javax.swing.GroupLayout.PREFERRED_SIZE, javax.swing.GroupLayout.DEFAULT_SIZE, javax.swing.GroupLayout.PREFERRED_SIZE)
                    .addComponent(jLabel4)
                    .addComponent(visitVariance, javax.swing.GroupLayout.PREFERRED_SIZE, javax.swing.GroupLayout.DEFAULT_SIZE, javax.swing.GroupLayout.PREFERRED_SIZE))
                .addGap(18, 18, Short.MAX_VALUE)
                .addGroup(visitSettingsPanelLayout.createParallelGroup(javax.swing.GroupLayout.Alignment.BASELINE)
                    .addComponent(jLabel5)
                    .addComponent(visitLimitField, javax.swing.GroupLayout.PREFERRED_SIZE, javax.swing.GroupLayout.DEFAULT_SIZE, javax.swing.GroupLayout.PREFERRED_SIZE))
                .addPreferredGap(javax.swing.LayoutStyle.ComponentPlacement.RELATED, 18, Short.MAX_VALUE)
                .addGroup(visitSettingsPanelLayout.createParallelGroup(javax.swing.GroupLayout.Alignment.BASELINE)
                    .addComponent(jLabel6)
                    .addComponent(totalLimitLabel))
                .addPreferredGap(javax.swing.LayoutStyle.ComponentPlacement.UNRELATED, javax.swing.GroupLayout.DEFAULT_SIZE, Short.MAX_VALUE)
                .addGroup(visitSettingsPanelLayout.createParallelGroup(javax.swing.GroupLayout.Alignment.BASELINE)
                    .addComponent(jButton3, javax.swing.GroupLayout.PREFERRED_SIZE, 39, javax.swing.GroupLayout.PREFERRED_SIZE)
                    .addComponent(jButton1, javax.swing.GroupLayout.PREFERRED_SIZE, 39, javax.swing.GroupLayout.PREFERRED_SIZE))
                .addContainerGap(javax.swing.GroupLayout.DEFAULT_SIZE, Short.MAX_VALUE))
        );

        javax.swing.GroupLayout limitDialogLayout = new javax.swing.GroupLayout(limitDialog.getContentPane());
        limitDialog.getContentPane().setLayout(limitDialogLayout);
        limitDialogLayout.setHorizontalGroup(
            limitDialogLayout.createParallelGroup(javax.swing.GroupLayout.Alignment.LEADING)
            .addGap(0, 284, Short.MAX_VALUE)
            .addGroup(limitDialogLayout.createParallelGroup(javax.swing.GroupLayout.Alignment.LEADING)
                .addComponent(visitSettingsPanel, javax.swing.GroupLayout.DEFAULT_SIZE, javax.swing.GroupLayout.DEFAULT_SIZE, Short.MAX_VALUE))
        );
        limitDialogLayout.setVerticalGroup(
            limitDialogLayout.createParallelGroup(javax.swing.GroupLayout.Alignment.LEADING)
            .addGap(0, 188, Short.MAX_VALUE)
            .addGroup(limitDialogLayout.createParallelGroup(javax.swing.GroupLayout.Alignment.LEADING)
                .addComponent(visitSettingsPanel, javax.swing.GroupLayout.DEFAULT_SIZE, javax.swing.GroupLayout.DEFAULT_SIZE, Short.MAX_VALUE))
        );

        setMaximumSize(new java.awt.Dimension(704, 418));
        setMinimumSize(new java.awt.Dimension(704, 418));
        setPreferredSize(new java.awt.Dimension(704, 418));
        addComponentListener(new java.awt.event.ComponentAdapter() {
            public void componentShown(java.awt.event.ComponentEvent evt) {
                formComponentShown(evt);
            }
        });

        saveAsButton.setFont(new java.awt.Font("Tahoma", 1, 12));
        saveAsButton.setText("Сохранить Как...");
        saveAsButton.setMargin(new java.awt.Insets(2, 10, 2, 10));
        saveAsButton.addActionListener(new java.awt.event.ActionListener() {
            public void actionPerformed(java.awt.event.ActionEvent evt) {
                saveAsButtonActionPerformed(evt);
            }
        });

        fromDate.setFormatterFactory(new javax.swing.text.DefaultFormatterFactory(new javax.swing.text.DateFormatter(new java.text.SimpleDateFormat("dd.MM.yyyy"))));
        fromDate.setText("ДД.ММ.ГГГГ");
        fromDate.setFont(new java.awt.Font("Tahoma", 0, 14));
        fromDate.addFocusListener(new java.awt.event.FocusAdapter() {
            public void focusGained(java.awt.event.FocusEvent evt) {
                fromDateFocusGained(evt);
            }
            public void focusLost(java.awt.event.FocusEvent evt) {
                fromDateFocusLost(evt);
            }
        });

        jLabel1.setFont(new java.awt.Font("Tahoma", 0, 14));
        jLabel1.setText(Dictionary.DATES);

        toDate.setFormatterFactory(new javax.swing.text.DefaultFormatterFactory(new javax.swing.text.DateFormatter(new java.text.SimpleDateFormat("dd.MM.yyyy"))));
        toDate.setText("ДД.ММ.ГГГГ");
        toDate.setFont(new java.awt.Font("Tahoma", 0, 14));
        toDate.addFocusListener(new java.awt.event.FocusAdapter() {
            public void focusGained(java.awt.event.FocusEvent evt) {
                toDateFocusGained(evt);
            }
            public void focusLost(java.awt.event.FocusEvent evt) {
                toDateFocusLost(evt);
            }
        });

        showReport.setFont(new java.awt.Font("Tahoma", 1, 14));
        showReport.setText(Dictionary.SHOW);
        showReport.addMouseListener(new java.awt.event.MouseAdapter() {
            public void mouseClicked(java.awt.event.MouseEvent evt) {
                showReportMouseClicked(evt);
            }
        });
        showReport.addActionListener(new java.awt.event.ActionListener() {
            public void actionPerformed(java.awt.event.ActionEvent evt) {
                showReportActionPerformed(evt);
            }
        });

        jCheckBox1.setFont(new java.awt.Font("Tahoma", 0, 14));
        jCheckBox1.setText(Dictionary.IN_NEW_WINDOW);

        jButton2.setText("-");
        jButton2.setToolTipText("");
        jButton2.setMargin(new java.awt.Insets(2, 2, 2, 2));
        jButton2.addActionListener(new java.awt.event.ActionListener() {
            public void actionPerformed(java.awt.event.ActionEvent evt) {
                jButton2ActionPerformed(evt);
            }
        });

        reports.setTabPlacement(javax.swing.JTabbedPane.BOTTOM);
        reports.setFont(new java.awt.Font("Tahoma", 1, 14));

        vakcTable.setAutoCreateRowSorter(true);
        vakcTable.setFont(vakcTable.getFont().deriveFont(vakcTable.getFont().getSize()+1f));
        vakcTable.setModel(new javax.swing.table.DefaultTableModel(
            new Object [][] {

            },
            new String [] {
                Dictionary.NEXT_VAKC, Dictionary.VAKC_MADE, Dictionary.VAKCINE,
                Dictionary.CLIENT
            }
        ) {
            Class[] types = new Class [] {
                java.lang.Object.class, java.lang.Object.class, java.lang.Object.class, java.lang.String.class
            };
            boolean[] canEdit = new boolean [] {
                false, false, false, false
            };

            public Class getColumnClass(int columnIndex) {
                return types [columnIndex];
            }

            public boolean isCellEditable(int rowIndex, int columnIndex) {
                return canEdit [columnIndex];
            }
        });
        vakcTable.setRowHeight(20);
        vakcTable.setShowHorizontalLines(false);
        vakcTable.setShowVerticalLines(false);
        jScrollPane2.setViewportView(vakcTable);
        vakcTable.getColumnModel().getColumn(0).setMinWidth(100);
        vakcTable.getColumnModel().getColumn(0).setMaxWidth(100);
        vakcTable.getColumnModel().getColumn(1).setMinWidth(120);
        vakcTable.getColumnModel().getColumn(1).setMaxWidth(120);
        vakcTable.getColumnModel().getColumn(2).setPreferredWidth(50);
        vakcTable.getColumnModel().getColumn(3).setPreferredWidth(120);

        javax.swing.GroupLayout jPanel1Layout = new javax.swing.GroupLayout(jPanel1);
        jPanel1.setLayout(jPanel1Layout);
        jPanel1Layout.setHorizontalGroup(
            jPanel1Layout.createParallelGroup(javax.swing.GroupLayout.Alignment.LEADING)
            .addComponent(jScrollPane2, javax.swing.GroupLayout.DEFAULT_SIZE, 699, Short.MAX_VALUE)
        );
        jPanel1Layout.setVerticalGroup(
            jPanel1Layout.createParallelGroup(javax.swing.GroupLayout.Alignment.LEADING)
            .addComponent(jScrollPane2, javax.swing.GroupLayout.Alignment.TRAILING, javax.swing.GroupLayout.DEFAULT_SIZE, 342, Short.MAX_VALUE)
        );

        reports.addTab(Dictionary.VAKCINES, jPanel1);

        visitSplitter.setDividerLocation(124);
        visitSplitter.setDividerSize(8);
        visitSplitter.setOrientation(javax.swing.JSplitPane.VERTICAL_SPLIT);
        visitSplitter.setContinuousLayout(true);
        visitSplitter.setDoubleBuffered(true);
        visitSplitter.setOneTouchExpandable(true);
        visitSplitter.addMouseListener(new java.awt.event.MouseAdapter() {
            public void mouseClicked(java.awt.event.MouseEvent evt) {
                visitSplitterMouseClicked(evt);
            }
        });

        profitReportTable.setAutoCreateRowSorter(true);
        profitReportTable.setFont(profitReportTable.getFont().deriveFont(profitReportTable.getFont().getSize()+1f));
        profitReportTable.setModel(new javax.swing.table.DefaultTableModel(
            new Object [][] {

            },
            new String [] {
                Dictionary.DATE,
                Dictionary.WEEK_DAY,
                Dictionary.VISIT,
                Dictionary.DRUG_PROFIT,
                Dictionary.OPERATIONS,
                Dictionary.OTHER,
                Dictionary.PROFIT
            }
        ) {
            Class[] types = new Class [] {
                java.lang.Object.class, java.lang.Object.class, java.lang.Double.class, java.lang.Double.class, java.lang.Double.class, java.lang.Double.class, java.lang.Double.class
            };
            boolean[] canEdit = new boolean [] {
                false, false, false, false, false, false, false
            };

            public Class getColumnClass(int columnIndex) {
                return types [columnIndex];
            }

            public boolean isCellEditable(int rowIndex, int columnIndex) {
                return canEdit [columnIndex];
            }
        });
        profitReportTable.setRowHeight(20);
        profitReportTable.setShowVerticalLines(false);
        jScrollPane3.setViewportView(profitReportTable);

        visitSplitter.setBottomComponent(jScrollPane3);

        profitOverallTable.setFont(profitOverallTable.getFont().deriveFont(profitOverallTable.getFont().getSize()+1f));
        profitOverallTable.setModel(new javax.swing.table.DefaultTableModel(
            new Object [][] {
                {"Лекарства", null, null, null},
                {"Операции", null, null, null},
                {"Визиты", null, null, null},
                {"Другое", null, null, null},
                {"Всего", null, null, null}
            },
            new String [] {
                "Работы", "З.Цена", "Пр.Цена", "Прибыль"
            }
        ) {
            Class[] types = new Class [] {
                java.lang.String.class, java.lang.Double.class, java.lang.Double.class, java.lang.Double.class
            };
            boolean[] canEdit = new boolean [] {
                true, true, false, false
            };

            public Class getColumnClass(int columnIndex) {
                return types [columnIndex];
            }

            public boolean isCellEditable(int rowIndex, int columnIndex) {
                return canEdit [columnIndex];
            }
        });
        profitOverallTable.setRowHeight(20);
        jScrollPane1.setViewportView(profitOverallTable);

        visitSplitter.setLeftComponent(jScrollPane1);

        javax.swing.GroupLayout jPanel2Layout = new javax.swing.GroupLayout(jPanel2);
        jPanel2.setLayout(jPanel2Layout);
        jPanel2Layout.setHorizontalGroup(
            jPanel2Layout.createParallelGroup(javax.swing.GroupLayout.Alignment.LEADING)
            .addComponent(visitSplitter, javax.swing.GroupLayout.DEFAULT_SIZE, 699, Short.MAX_VALUE)
        );
        jPanel2Layout.setVerticalGroup(
            jPanel2Layout.createParallelGroup(javax.swing.GroupLayout.Alignment.LEADING)
            .addComponent(visitSplitter, javax.swing.GroupLayout.DEFAULT_SIZE, 342, Short.MAX_VALUE)
        );

        reports.addTab(Dictionary.PROFIT, jPanel2);

        visitReportTable.setAutoCreateRowSorter(true);
        visitReportTable.setFont(visitReportTable.getFont().deriveFont(visitReportTable.getFont().getSize()+1f));
        visitReportTable.setModel(new javax.swing.table.DefaultTableModel(
            new Object [][] {

            },
            new String [] {
                Dictionary.DATE,
                Dictionary.SURNAME,
                Dictionary.VISIT_PAYED,
                Dictionary.DRUG_PAYED,
                Dictionary.TOTAL_PAYED,
                Dictionary.DRUG_noNSO,
                Dictionary.VISIT_noNSO,
                Dictionary.TAX_TOTAL,
                Dictionary.TOTAL
            }
        ) {
            Class[] types = new Class [] {
                java.lang.Object.class, java.lang.Object.class,
                java.lang.Double.class, java.lang.Double.class,
                java.lang.Double.class, java.lang.Double.class,
                java.lang.Double.class, java.lang.Double.class,
                java.lang.Double.class
            };
            boolean[] canEdit = new boolean [] {
                false, false, false, false, false, false, false, false, false
            };

            public Class getColumnClass(int columnIndex) {
                return types [columnIndex];
            }

            public boolean isCellEditable(int rowIndex, int columnIndex) {
                return canEdit [columnIndex];
            }
        });
        visitReportTable.setRowHeight(20);
        jScrollPane4.setViewportView(visitReportTable);
        visitReportTable.getColumnModel().getColumn(1).setMinWidth(100);

        javax.swing.GroupLayout jPanel3Layout = new javax.swing.GroupLayout(jPanel3);
        jPanel3.setLayout(jPanel3Layout);
        jPanel3Layout.setHorizontalGroup(
            jPanel3Layout.createParallelGroup(javax.swing.GroupLayout.Alignment.LEADING)
            .addComponent(jScrollPane4, javax.swing.GroupLayout.DEFAULT_SIZE, 699, Short.MAX_VALUE)
        );
        jPanel3Layout.setVerticalGroup(
            jPanel3Layout.createParallelGroup(javax.swing.GroupLayout.Alignment.LEADING)
            .addComponent(jScrollPane4, javax.swing.GroupLayout.DEFAULT_SIZE, 342, Short.MAX_VALUE)
        );

        reports.addTab(Dictionary.VISITS, jPanel3);

        diagTable.setAutoCreateRowSorter(true);
        diagTable.setFont(diagTable.getFont().deriveFont(diagTable.getFont().getSize()+1f));
        diagTable.setModel(new javax.swing.table.DefaultTableModel(
            new Object [][] {

            },
            new String [] {
                "Название", "Кол-во"
            }
        ) {
            Class[] types = new Class [] {
                java.lang.Object.class, java.lang.Integer.class
            };
            boolean[] canEdit = new boolean [] {
                false, false
            };

            public Class getColumnClass(int columnIndex) {
                return types [columnIndex];
            }

            public boolean isCellEditable(int rowIndex, int columnIndex) {
                return canEdit [columnIndex];
            }
        });
        diagTable.setRowHeight(20);
        jScrollPane6.setViewportView(diagTable);

        withPredv.setFont(new java.awt.Font("Tahoma", 0, 14));
        withPredv.setText(Dictionary.COUNT_PREDV);
        withPredv.setHorizontalAlignment(javax.swing.SwingConstants.RIGHT);

        javax.swing.GroupLayout jPanel4Layout = new javax.swing.GroupLayout(jPanel4);
        jPanel4.setLayout(jPanel4Layout);
        jPanel4Layout.setHorizontalGroup(
            jPanel4Layout.createParallelGroup(javax.swing.GroupLayout.Alignment.LEADING)
            .addComponent(jScrollPane6, javax.swing.GroupLayout.Alignment.TRAILING, javax.swing.GroupLayout.DEFAULT_SIZE, 699, Short.MAX_VALUE)
            .addGroup(javax.swing.GroupLayout.Alignment.TRAILING, jPanel4Layout.createSequentialGroup()
                .addContainerGap(462, Short.MAX_VALUE)
                .addComponent(withPredv, javax.swing.GroupLayout.PREFERRED_SIZE, 231, javax.swing.GroupLayout.PREFERRED_SIZE)
                .addGap(6, 6, 6))
        );
        jPanel4Layout.setVerticalGroup(
            jPanel4Layout.createParallelGroup(javax.swing.GroupLayout.Alignment.LEADING)
            .addGroup(jPanel4Layout.createSequentialGroup()
                .addComponent(jScrollPane6, javax.swing.GroupLayout.DEFAULT_SIZE, 317, Short.MAX_VALUE)
                .addGap(0, 0, 0)
                .addComponent(withPredv))
        );

        reports.addTab(Dictionary.DIAGNOSES, jPanel4);

        javax.swing.GroupLayout jPanel5Layout = new javax.swing.GroupLayout(jPanel5);
        jPanel5.setLayout(jPanel5Layout);
        jPanel5Layout.setHorizontalGroup(
            jPanel5Layout.createParallelGroup(javax.swing.GroupLayout.Alignment.LEADING)
            .addComponent(reports, javax.swing.GroupLayout.DEFAULT_SIZE, 704, Short.MAX_VALUE)
        );
        jPanel5Layout.setVerticalGroup(
            jPanel5Layout.createParallelGroup(javax.swing.GroupLayout.Alignment.LEADING)
            .addComponent(reports, javax.swing.GroupLayout.DEFAULT_SIZE, 373, Short.MAX_VALUE)
        );

        javax.swing.GroupLayout layout = new javax.swing.GroupLayout(this);
        this.setLayout(layout);
        layout.setHorizontalGroup(
            layout.createParallelGroup(javax.swing.GroupLayout.Alignment.LEADING)
            .addGroup(javax.swing.GroupLayout.Alignment.TRAILING, layout.createSequentialGroup()
                .addContainerGap()
                .addComponent(jLabel1, javax.swing.GroupLayout.PREFERRED_SIZE, 73, javax.swing.GroupLayout.PREFERRED_SIZE)
                .addPreferredGap(javax.swing.LayoutStyle.ComponentPlacement.RELATED)
                .addComponent(fromDate, javax.swing.GroupLayout.PREFERRED_SIZE, 88, javax.swing.GroupLayout.PREFERRED_SIZE)
                .addPreferredGap(javax.swing.LayoutStyle.ComponentPlacement.RELATED)
                .addComponent(jButton2, javax.swing.GroupLayout.PREFERRED_SIZE, 17, javax.swing.GroupLayout.PREFERRED_SIZE)
                .addPreferredGap(javax.swing.LayoutStyle.ComponentPlacement.UNRELATED)
                .addComponent(toDate, javax.swing.GroupLayout.PREFERRED_SIZE, 92, javax.swing.GroupLayout.PREFERRED_SIZE)
                .addPreferredGap(javax.swing.LayoutStyle.ComponentPlacement.RELATED)
                .addComponent(jCheckBox1, javax.swing.GroupLayout.PREFERRED_SIZE, 124, javax.swing.GroupLayout.PREFERRED_SIZE)
                .addPreferredGap(javax.swing.LayoutStyle.ComponentPlacement.RELATED, 14, Short.MAX_VALUE)
                .addComponent(saveAsButton)
                .addPreferredGap(javax.swing.LayoutStyle.ComponentPlacement.UNRELATED)
                .addComponent(showReport))
            .addComponent(jPanel5, javax.swing.GroupLayout.Alignment.TRAILING, javax.swing.GroupLayout.DEFAULT_SIZE, javax.swing.GroupLayout.DEFAULT_SIZE, Short.MAX_VALUE)
        );
        layout.setVerticalGroup(
            layout.createParallelGroup(javax.swing.GroupLayout.Alignment.LEADING)
            .addGroup(layout.createSequentialGroup()
                .addContainerGap()
                .addGroup(layout.createParallelGroup(javax.swing.GroupLayout.Alignment.BASELINE)
                    .addComponent(jLabel1)
                    .addComponent(fromDate, javax.swing.GroupLayout.PREFERRED_SIZE, javax.swing.GroupLayout.DEFAULT_SIZE, javax.swing.GroupLayout.PREFERRED_SIZE)
                    .addComponent(jCheckBox1)
                    .addComponent(toDate, javax.swing.GroupLayout.PREFERRED_SIZE, javax.swing.GroupLayout.DEFAULT_SIZE, javax.swing.GroupLayout.PREFERRED_SIZE)
                    .addComponent(jButton2, javax.swing.GroupLayout.PREFERRED_SIZE, 25, javax.swing.GroupLayout.PREFERRED_SIZE)
                    .addComponent(showReport, javax.swing.GroupLayout.PREFERRED_SIZE, 27, javax.swing.GroupLayout.PREFERRED_SIZE)
                    .addComponent(saveAsButton, javax.swing.GroupLayout.PREFERRED_SIZE, 27, javax.swing.GroupLayout.PREFERRED_SIZE))
                .addPreferredGap(javax.swing.LayoutStyle.ComponentPlacement.RELATED)
                .addComponent(jPanel5, javax.swing.GroupLayout.DEFAULT_SIZE, javax.swing.GroupLayout.DEFAULT_SIZE, Short.MAX_VALUE))
        );
    }// </editor-fold>//GEN-END:initComponents

    private ArrayList<ProfitReport> limitProfitReport(ArrayList<ProfitReport> overRep) {
        double profLimit = Double.parseDouble(drugLimitField.getText());
        double var = 0;
        if(!visitVariance.getText().isEmpty())
         var = Double.parseDouble(visitVariance.getText());
        double curLimit = 0;
        ArrayList<ProfitReport> newRep = new ArrayList<ProfitReport>();
        Random rand = new Random();
        int tr = 0;
        int rnd = 0;
        try
        {
        while(curLimit < (profLimit - var) || curLimit > (profLimit + var) )
        {
            rnd = rand.nextInt((overRep.size()-1));//(int)(Math.random()*(overRep.size()-1));
            if( (curLimit + overRep.get(rnd).getOverall()) < (profLimit +var) )
            {
                //tr = 0;
                curLimit += overRep.get(rnd).getOverall();
                newRep.add(overRep.get(rnd));
                overRep.remove(rnd);
            }else
            {
                tr++;
                if(tr >= 2*overRep.size())
                   throw new Exception("Неудалось составить выбранную прибыль с указанным отклонением, попробуйте еще раз");
                
                rnd = rand.nextInt((newRep.size()-1));
                curLimit -= newRep.get(rnd).getOverall();
                overRep.add(newRep.get(rnd));
                newRep.remove(rnd);
                
            }
            if(overRep.size() == 0)
                throw new Exception("Недостаточно визитов для выбранной прибыли");
        }
        } catch(Exception ex)
        {
            frame.FormException(this, ex.getMessage());
        }
        return newRep;
    }
    private void showReport()
    {
        String reportName = reports.getTitleAt(reports.getSelectedIndex());

        SimpleDateFormat sdf = new SimpleDateFormat("dd.MM.yyyy");

        try {
            dateFrom = new Date(sdf.parse(fromDate.getText()).getTime());
        } catch (ParseException ex) {
            dateFrom = new Date(0);
            dateTo = new Date(Calendar.getInstance().getTime().getTime());
        }

        try {
            dateTo = new Date(sdf.parse(toDate.getText()).getTime());
        } catch (ParseException ex) {
            if(dateFrom.getTime() != 0)
                dateTo = new Date(0);
            else
                dateTo = new Date(Calendar.getInstance().getTime().getTime());
        }
        Date temp;
        if( dateFrom.after(dateTo))
        {
            temp = dateFrom;
            dateFrom = dateTo;
            dateTo = temp;
        }
        if(dateTo.before(dateFrom))
        {
            temp = dateTo;
            dateTo = dateFrom;
            dateFrom = temp;
        }
        
          if(reportName.equals(Dictionary.VAKCINES))
              showVakcine();
          else if(reportName.equals(Dictionary.PROFIT))
              showProfitReport();
          else if(reportName.equals(Dictionary.VISITS))
              showVisitReport();
          else if(reportName.equals(Dictionary.DIAGNOSES))
              showDiagReport();
    }

    private void showDiagReport()
    {
        DefaultTableModel model = (DefaultTableModel)diagTable.getModel();
        model.setRowCount(0);
        try
        {
          ArrayList<DiagnoseReport> rep = Reports.getDiagReport(dateFrom, dateTo, withPredv.isSelected());
          for(int i=0; i< rep.size(); i++)
          {
              model.addRow(new Object[] {rep.get(i).getDiagnose().getName() +
                                        " (" +rep.get(i).getDiagnose().getRusName() + ") ",
                                         rep.get(i).getAmount()});
          }
          System.out.println(rep.size());
          diagTable.setModel(model);
        } catch(SQLException e)
        {
            frame.SQLException(e);
        }
        

    }
    private double getLimitedCrit(VisitReport rep)
    {
        return rep.getDrugPrice();
    }
    private ArrayList<ArrayList<VisitReport>> limitVisitReport(ArrayList<ArrayList<VisitReport>> oldReport)
    {
        try
        {
          double drugLimit = Double.parseDouble(this.drugLimitField.getText());
          double visitLimit = Double.parseDouble(this.visitLimitField.getText());
        
            double var = 0;
            if(!visitVariance.getText().isEmpty())
               var = Double.parseDouble(visitVariance.getText());
            else
               throw new Exception("Не указано отклонение");
            
        double curDrugLimit = 0;
        double curVisitLimit=0;
        double cardVisitLimit=0;
        ArrayList<VisitReport> newList = new ArrayList<VisitReport>();
        ArrayList<VisitReport> cardList = new ArrayList<VisitReport>();
        ArrayList<VisitReport> rawReports = new ArrayList<VisitReport>();
        ArrayList<ArrayList<VisitReport>> result = new ArrayList<ArrayList<VisitReport>>();
        
        for(int i=0; i< oldReport.size(); i++)
            rawReports.addAll(oldReport.get(i));

        // select all vists that were payed by card to the separate list
        Iterator<VisitReport> iter = rawReports.iterator();
        while(iter.hasNext())
        {
            VisitReport rep = iter.next();
            if(rep.isPayedByCard())
            {
                // increase current limit by amount of this card payment
                curDrugLimit += getLimitedCrit(rep);
                //curVisitLimit += rep.getVisitOverallPrice();
                cardVisitLimit += rep.getVisitOverallPrice();
                cardList.add(rep);
                iter.remove();
            }
        }
        System.out.println("Card Size:" + cardList.size());
        Random rand = new Random();
        int tr = 0,
            rndClient = 0;
        
            if(curDrugLimit > (drugLimit + var))
                throw new Exception("Сумма карточных операций("+curDrugLimit+") больше указанного лимита. ");
        while(curDrugLimit < (drugLimit - var) || curDrugLimit > (drugLimit + var) )
        {
            if(rawReports.size() > 1)
              rndClient = rand.nextInt(rawReports.size());
            else
              rndClient = 0;

            // if visit sum is not enough
            if( (curDrugLimit + getLimitedCrit(rawReports.get(rndClient))) <= (drugLimit + var) )
            {
                //tr=0;
                curDrugLimit += getLimitedCrit(rawReports.get(rndClient));
                curVisitLimit += rawReports.get(rndClient).getVisitOverallPrice();
                newList.add(rawReports.get(rndClient));
                rawReports.remove(rndClient);
            // if visit sum is too much
            }else
            {
                tr++;
                if(tr >= 2*rawReports.size())
                   throw new Exception("Неудалось составить отчет с указанным отклонением, попробуйте еще раз");

                if(newList.size() == 0)
                    continue;
                if(newList.size() > 1)
                    rndClient = rand.nextInt(newList.size());
                else
                    rndClient = 0;
                // remove random visit from the newList
                curDrugLimit -= getLimitedCrit(newList.get(rndClient));
                curVisitLimit -= rawReports.get(rndClient).getVisitOverallPrice();
                rawReports.add(newList.get(rndClient));
                newList.remove(rndClient);
            }
            if(rawReports.size() == 0)
                throw new Exception("Недостаточно визитов для выбранного ограничения");
        }
        // add Visits payed by card
        newList.addAll(cardList);
        System.out.println("CurVis: "+curVisitLimit);

        double visitKoef = (visitLimit-cardVisitLimit)/(curVisitLimit);
        System.out.println("Koef: "+visitKoef);
        
        TreeMap<Date,ArrayList<VisitReport>> newRep = new
                TreeMap<Date,ArrayList<VisitReport>>(Collections.reverseOrder());
        for(int i=0; i<newList.size(); i++)
        {
            ArrayList<VisitReport> reps = newRep.get(newList.get(i).getDate());
            if(reps == null)
                reps = new ArrayList<VisitReport>();
            if(!newList.get(i).isPayedByCard())
            {
                newList.get(i).setVisitPrice(Dogy.roundPrice(newList.get(i).getVisitPrice()*visitKoef));
                newList.get(i).setOperPrice(Dogy.roundPrice(newList.get(i).getOperPrice()*visitKoef));
            }
            
            reps.add(newList.get(i));
            newRep.put(newList.get(i).getDate(), reps);
        }
        Collection c = newRep.values();
        Iterator itr = c.iterator();
        while(itr.hasNext())
            result.add((ArrayList)itr.next());

        return result;
        
        }catch(Exception e)
        {
            frame.FormException(this,e.getMessage());
            e.printStackTrace();
            return null;
        }
        
    }
    public void showVisitReport()
    {
        DefaultTableModel model = (DefaultTableModel)visitReportTable.getModel();
        model.setRowCount(0);
        try
        {

        Dogy.incrLoadingProgress(10,"tableRec","Подготовка");
        ArrayList<ArrayList<VisitReport>> dayRep = Reports.getVisitReport(dateFrom, dateTo);
        Dogy.incrLoadingProgress(50,"tableRec","Добавление в таблицу");

        ArrayList<VisitReport> visRep;
        VisitReport overall = new VisitReport();
        VisitReport perDay = new VisitReport();
        Date date =null;
        int i=0;
        if(!drugLimitField.getText().isEmpty() && !drugLimitField.getText().isEmpty())
        {
            dayRep = limitVisitReport(dayRep);
            visitReportTable.setBorder(javax.swing.BorderFactory.createLineBorder(new java.awt.Color(255, 0, 0), 3));
        }else
            visitReportTable.setBorder(null);
        for(int m=0; m< dayRep.size(); m++)
        {
            visRep = dayRep.get(m);
            for(i=0; i < visRep.size(); i++)
            {
                model.addRow( new Object[] {visRep.get(i).getDate(),
                                            visRep.get(i),
                                            visRep.get(i).getVisitOverallPrice(),
                                            visRep.get(i).getDrugPrice(),
                                            visRep.get(i).getTotalPrice(),
                                            visRep.get(i).getDrugTaxed(),
                                            visRep.get(i).getVisitOverallTaxed(),
                                            visRep.get(i).getOverallTaxPrice(),
                                            visRep.get(i).getTotalIncome()
                                             });
                overall.setVisitPrice(overall.getVisitPrice() + visRep.get(i).getVisitOverallPrice());
                overall.setDrugPrice(overall.getDrugPrice() + visRep.get(i).getDrugPrice());
                overall.setDrugTaxed(overall.getDrugTaxed() + visRep.get(i).getDrugTaxed());
                perDay.setVisitPrice(perDay.getVisitPrice() + visRep.get(i).getVisitOverallPrice());
                perDay.setDrugPrice(perDay.getDrugPrice() + visRep.get(i).getDrugPrice());
                perDay.setDrugTaxed(perDay.getDrugTaxed() + visRep.get(i).getDrugTaxed());

            }// for
            model.addRow( new Object[] {    null,
                                            "<html><i><b>Итого за день</b></i>",
                                            perDay.getVisitOverallPrice(),
                                            perDay.getDrugPrice(),
                                            perDay.getTotalPrice(),
                                            perDay.getDrugTaxed(),
                                            perDay.getVisitOverallTaxed(),
                                            perDay.getOverallTaxPrice(),
                                            perDay.getTotalIncome()
                                             });
            perDay = new VisitReport();
        }// day for
        
        
        model.addRow( new Object[] {    null,
                                            "<html><b>ВСЕГО</b>",
                                            overall.getVisitOverallPrice(),
                                            overall.getDrugPrice(),
                                            overall.getTotalPrice(),
                                            overall.getDrugTaxed(),
                                            overall.getVisitOverallTaxed(),
                                            overall.getOverallTaxPrice(),
                                            overall.getTotalIncome()
                                             });
        
        Dogy.incrLoadingProgress(100,"tableRec","Завершение");
        }catch(Exception e)
        {
            System.out.println(e);
            e.printStackTrace();
        }
    }

    private void writeExcelVisitReport(File file)
    {
        WritableWorkbook wrk=null;
        WritableSheet sheet=null;
        Label label;
        jxl.write.Number number;
        jxl.write.Formula formula;
        WritableCellFormat cf;
        WritableFont times16font;
        int pos=2, lDay = 0;
        try
        {
                int dateIndex = visitReportTable.getColumnModel().getColumnIndex(Dictionary.DATE),
                    reportIndex = visitReportTable.getColumnModel().getColumnIndex(Dictionary.SURNAME),
                    visitIndex =   visitReportTable.getColumnModel().getColumnIndex(Dictionary.VISIT_PAYED),
                    visitTaxedIndex =   visitReportTable.getColumnModel().getColumnIndex(Dictionary.VISIT_noNSO),
                    drugIndex =  visitReportTable.getColumnModel().getColumnIndex(Dictionary.DRUG_PAYED),
                    drugTaxedIndex =  visitReportTable.getColumnModel().getColumnIndex(Dictionary.DRUG_noNSO);
                    
                
                Dogy.incrLoadingProgress(75,"tableRec","Запись в Excel");
                wrk = writeVisitReportXLS(file);
                sheet = wrk.getSheet(0);

                String lastDate = "";
                for(int i=0; i< visitReportTable.getRowCount()-1; i++)
                {
                    if(visitReportTable.getValueAt(i, dateIndex) != null &&
                       !lastDate.equals(visitReportTable.getValueAt(i, dateIndex).toString()))
                    {
                        cf = new WritableCellFormat();
                        //cf.setBorder(Border.LEFT, BorderLineStyle.HAIR, Colour.BLACK);
                        times16font = new WritableFont(WritableFont.TAHOMA, 8, WritableFont.NO_BOLD);
                        cf.setFont(times16font);
                        
                        label = new Label(0, pos,
                                      visitReportTable.getValueAt(i, dateIndex).toString());
                        label.setCellFormat(cf);
                        sheet.addCell(label);
                        lastDate = visitReportTable.getValueAt(i, dateIndex).toString();
                    }
                    // new rec
                    if(visitReportTable.getValueAt(i, reportIndex) instanceof VisitReport)
                    {
                        VisitReport visitReport = (VisitReport)visitReportTable.getValueAt(i, reportIndex);
                        Double drugPrice = (Double)visitReportTable.getValueAt(i, drugIndex);
                        Double drugTaxed = (Double)visitReportTable.getValueAt(i, drugTaxedIndex);

                        Double visitPrice = (Double)visitReportTable.getValueAt(i, visitIndex);
                        Double visitTaxed = (Double)visitReportTable.getValueAt(i, visitTaxedIndex);

                        Double averageDrugTax = Dogy.DRUG_TAX/100;

                        Double averageVisitTax = Dogy.VAT_TAX/100;
                        
                        if(drugPrice == 0 ) averageDrugTax = Dogy.DRUG_TAX/100;
                        if(visitPrice == 0 ) averageVisitTax = Dogy.VAT_TAX/100;

                        cf = new WritableCellFormat();
                        //cf.setBorder(Border.LEFT, BorderLineStyle.HAIR, Colour.BLACK);
                        times16font = new WritableFont(WritableFont.TAHOMA, 8, WritableFont.NO_BOLD);
                        cf.setFont(times16font);
                            label = new Label(0, 2+pos, "");
                            label.setCellFormat(cf);
                            sheet.addCell(label);
                            label = new Label(1, pos, Dogy.convertToLatin(visitReport.getUser().getFullName()));
                            label.setCellFormat(cf);
                            sheet.addCell(label);
                            label = new Label(2, pos, Dogy.convertToLatin(visitReport.getUser().getStreet()));
                            label.setCellFormat(cf);
                            sheet.addCell(label);

                            // card
                            label = new Label(12, pos, visitReport.isPayedByCard() ? "p":"");
                            label.setCellFormat(cf);
                            sheet.addCell(label);

                            // Drugs without TAX
                            formula = new Formula(3, pos,
                                    //
                                    CellReferenceHelper.getColumnReference(9)+(pos+1)+
                                    "/(1+"+ averageDrugTax +")"
                                    );
                            //System.out.println(drugIndex + " " +averageDrugTax);
                            //number = new Number(3, pos, visRep.get(i).getDrugTaxed());
                            formula.setCellFormat(cf);
                            sheet.addCell(formula);

                            // Drugs KM
                            formula = new Formula(4, pos,
                                    //
                                    CellReferenceHelper.getColumnReference(9)+(pos+1)+
                                    "-"+
                                    CellReferenceHelper.getColumnReference(3)+(pos+1)
                                    );
                            //number = new Number(4, pos, visRep.get(i).getDrugTaxPrice());
                            formula.setCellFormat(cf);
                            sheet.addCell(formula);
                            //
                            formula = new Formula(5, pos,
                                    //
                                    CellReferenceHelper.getColumnReference(10)+(pos+1)+
                                    "/(1+"+ averageVisitTax +")"
                                    );
                            //number = new Number(5, pos, visRep.get(i).getVisitOverallTaxed());
                            formula.setCellFormat(cf);
                            sheet.addCell(formula);

                            formula = new Formula(6, pos,
                                    //
                                    CellReferenceHelper.getColumnReference(10)+(pos+1)+
                                    "-"+
                                    CellReferenceHelper.getColumnReference(5)+(pos+1)
                                    );
                            //number = new Number(6, pos, visRep.get(i).getVisitTaxPrice());
                            formula.setCellFormat(cf);
                            sheet.addCell(formula);

                            formula = new Formula(7, pos,
                                    //
                                    CellReferenceHelper.getColumnReference(3)+(pos+1)+
                                    "+"+
                                    CellReferenceHelper.getColumnReference(5)+(pos+1)
                                    );
                            //number = new Number(7, pos, visRep.get(i).getTotalIncome());
                            formula.setCellFormat(cf);
                            sheet.addCell(formula);

                            formula = new Formula(8, pos,
                                    //
                                    CellReferenceHelper.getColumnReference(4)+(pos+1)+
                                    "+"+
                                    CellReferenceHelper.getColumnReference(6)+(pos+1)
                                    );
                            //number = new Number(8, pos, visRep.get(i).getOverallTaxPrice());
                            formula.setCellFormat(cf);
                            sheet.addCell(formula);

                            number = new Number(9, pos, drugPrice);
                            number.setCellFormat(cf);
                            sheet.addCell(number);

                            number = new Number(10, pos, visitPrice);
                            number.setCellFormat(cf);
                            sheet.addCell(number);

                            
                            formula = new Formula(11, pos,

                                    CellReferenceHelper.getColumnReference(9)+(pos+1)+
                                    "+"+
                                    CellReferenceHelper.getColumnReference(10)+(pos+1)
                                    );
                            //number = new Number(11, pos, visRep.get(i).getTotalPrice());
                            formula.setCellFormat(cf);
                            sheet.addCell(formula);


                            sheet.getRowView(pos).setHidden(true);
                            pos++;
                            lDay++;
                    } // ^^^^^^ new rec
                    else
                    //new day
                    {
                        // day total
                        cf = new WritableCellFormat();
                        //cf.setBorder(Border.BOTTOM, BorderLineStyle.HAIR, Colour.BLACK);
                        //cf.setBorder(Border.LEFT, BorderLineStyle.HAIR, Colour.BLACK);
                        times16font = new WritableFont(WritableFont.TAHOMA, 8, WritableFont.BOLD);
                        cf.setFont(times16font);
                        label = new Label(2, pos, "Kokku",cf);
                        sheet.addCell(label);
                        Formula form;
                        for(int f=0; f<2;f++)
                           sheet.addCell(new Label(f,pos,"", cf));

                        for(int f=3; f<12;f++ )
                        {
                          form = new Formula(f,pos,"SUM("+CellReferenceHelper.getColumnReference(f)+pos+
                                                   ":"+CellReferenceHelper.getColumnReference(f)+(pos-lDay+1)+")",cf);
                          sheet.addCell(form);
                        }
                        pos++;
                        lDay = 0;
                        sheet.insertRow(pos);
                    }
                    
                }

                // global total
                cf = new WritableCellFormat();
                cf.setBorder(Border.BOTTOM, BorderLineStyle.MEDIUM, Colour.BLACK);
                times16font = new WritableFont(WritableFont.TAHOMA, 8, WritableFont.BOLD);
                cf.setFont(times16font);
                
                label = new Label(0, pos, "Kokku",cf);
                sheet.addCell(label);
                sheet.mergeCells(0, pos, 2, pos);
                
                Formula form;
                for(int f=3; f<12;f++ )
                {
                  form = new Formula(f,pos,"SUM("+CellReferenceHelper.getColumnReference(f)+pos+
                                           ":"+CellReferenceHelper.getColumnReference(f)+1+")/2",cf);
                  sheet.addCell(form);
                }
                wrk.write();
                wrk.close();
                Runtime.getRuntime().exec("explorer "+Dogy.REPORT_DIR);
        }catch(Exception e)
        {
            e.printStackTrace();
        }
    }

    
    private void showReportActionPerformed(java.awt.event.ActionEvent evt) {//GEN-FIRST:event_showReportActionPerformed

        Dogy.initLoading(2, "Загрузка", new Loadable(){
            @Override
            public Object load()
            {
                showReport();
                return null;
            }
            public void done(Object done)
            {
            }
            @Override
            public Object cancel()
            {
                try {
                    Database.cancelQuery();
                } catch (SQLException ex) {
                    ex.printStackTrace();
                }
                return null;
            }

            @Override
            public void progress(int p) {
                throw new UnsupportedOperationException("Not supported yet.");
            }
        });

        //showReport();
}//GEN-LAST:event_showReportActionPerformed

    private void fromDateFocusGained(java.awt.event.FocusEvent evt) {//GEN-FIRST:event_fromDateFocusGained
        if(fromDate.getValue() == null || fromDate.getText().equals("ДД.ММ.ГГГГ"))
        {
          fromDate.setText(dateText);
          if(fromDate.getFormatterFactory() == null)
          fromDate.setFormatterFactory(new javax.swing.text.DefaultFormatterFactory(new javax.swing.text.DateFormatter(new java.text.SimpleDateFormat("dd.MM.yyyy"))));
        }

        fromDate.setForeground(Color.black);
    }//GEN-LAST:event_fromDateFocusGained

    private void fromDateFocusLost(java.awt.event.FocusEvent evt) {//GEN-FIRST:event_fromDateFocusLost
        if(fromDate.getText().equals(""))
        {
            fromDate.setText("ДД.ММ.ГГГГ");
            fromDate.setForeground(Color.gray);
            fromDate.setFormatterFactory(null);
        }
    }//GEN-LAST:event_fromDateFocusLost

    private void toDateFocusGained(java.awt.event.FocusEvent evt) {//GEN-FIRST:event_toDateFocusGained
        if(toDate.getValue() == null || toDate.getText().equals("ДД.ММ.ГГГГ"))
        {
          if(toDate.getFormatterFactory() == null)
          toDate.setFormatterFactory(new javax.swing.text.DefaultFormatterFactory(new javax.swing.text.DateFormatter(new java.text.SimpleDateFormat("dd.MM.yyyy"))));
            if(fromDate.getValue() != null )
           {
            toDate.setValue(fromDate.getValue());
            toDate.setText(fromDate.getText());
            
           }
           else
            toDate.setText(dateText);

        }

        toDate.setForeground(Color.black);
    }//GEN-LAST:event_toDateFocusGained

    private void toDateFocusLost(java.awt.event.FocusEvent evt) {//GEN-FIRST:event_toDateFocusLost
        if(toDate.getText().equals(""))
        {
            toDate.setValue(null);
            toDate.setText("ДД.ММ.ГГГГ");
            toDate.setForeground(Color.gray);
            toDate.setFormatterFactory(null);
        }

    }//GEN-LAST:event_toDateFocusLost

    private void jButton2ActionPerformed(java.awt.event.ActionEvent evt) {//GEN-FIRST:event_jButton2ActionPerformed
          if(toDate.getFormatterFactory() == null)
          toDate.setFormatterFactory(new javax.swing.text.DefaultFormatterFactory(new javax.swing.text.DateFormatter(new java.text.SimpleDateFormat("dd.MM.yyyy"))));
            if((java.util.Date)fromDate.getValue() != null )
           {
            toDate.setValue(fromDate.getValue());
            toDate.setText(fromDate.getText());
            toDate.setForeground(Color.black);
           }
    }//GEN-LAST:event_jButton2ActionPerformed

    private void visitSplitterMouseClicked(java.awt.event.MouseEvent evt) {//GEN-FIRST:event_visitSplitterMouseClicked
        visitSplitter.setLastDividerLocation(105);
    }//GEN-LAST:event_visitSplitterMouseClicked

    private void showReportMouseClicked(java.awt.event.MouseEvent evt) {//GEN-FIRST:event_showReportMouseClicked
        

}//GEN-LAST:event_showReportMouseClicked

    private void drugLimitFieldFocusLost(java.awt.event.FocusEvent evt) {//GEN-FIRST:event_drugLimitFieldFocusLost
        if(drugLimitField.getText().isEmpty())
            drugLimitField.setValue(null);
}//GEN-LAST:event_drugLimitFieldFocusLost

    private void visitVarianceFocusLost(java.awt.event.FocusEvent evt) {//GEN-FIRST:event_visitVarianceFocusLost
        if(visitVariance.getText().isEmpty())
            visitVariance.setValue(null);
}//GEN-LAST:event_visitVarianceFocusLost

    private void formComponentShown(java.awt.event.ComponentEvent evt) {//GEN-FIRST:event_formComponentShown
        showReport.requestFocus();
    }//GEN-LAST:event_formComponentShown

    private void jButton1ActionPerformed(java.awt.event.ActionEvent evt) {//GEN-FIRST:event_jButton1ActionPerformed
        limitDialog.setVisible(false);
    }//GEN-LAST:event_jButton1ActionPerformed

    private void jButton3ActionPerformed(java.awt.event.ActionEvent evt) {//GEN-FIRST:event_jButton3ActionPerformed
        limitDialog.setVisible(false);
    }//GEN-LAST:event_jButton3ActionPerformed

    private void saveAsButtonActionPerformed(java.awt.event.ActionEvent evt) {//GEN-FIRST:event_saveAsButtonActionPerformed
          
        String reportName = reports.getTitleAt(reports.getSelectedIndex());
        fileChooser = new JFileChooser(Dogy.REPORT_DIR);
        fileChooser.setMultiSelectionEnabled(false);
        fileChooser.setFileFilter(new FileFilter() {

            @Override
            public boolean accept(File file) {
                if(file.isDirectory() || file.getName().contains("xls"))
                    return true;
                return false;
            }

            @Override
            public String getDescription() {
                return "Select excel *.xls file";
            }
        });
        
        SimpleDateFormat sdf = new SimpleDateFormat("dd.MM.yyyy");
        try
        {
            fileChooser.setSelectedFile(
                          new File(Dogy.REPORT_DIR +"\\"+reportName+"["+
                          sdf.format(dateFrom)+"-"+sdf.format(dateTo)+"]"+
                                                                       ".xls"));
        }
        catch(Exception e)
        {
            fileChooser.setSelectedFile(
                          new File(Dogy.REPORT_DIR +"\\"+reportName+".xls"));
        }
        int status =  fileChooser.showSaveDialog(this);


        if( status != JFileChooser.APPROVE_OPTION) return;
        
        if(reportName.equals(Dictionary.VAKCINES))
              writeExcelVakcineReport(fileChooser.getSelectedFile());
          else if(reportName.equals(Dictionary.PROFIT))
              ;
          else if(reportName.equals(Dictionary.VISITS))
              writeExcelVisitReport(fileChooser.getSelectedFile());
          else if(reportName.equals(Dictionary.DIAGNOSES))
              ;
}//GEN-LAST:event_saveAsButtonActionPerformed

    private void drugLimitFieldKeyReleased(java.awt.event.KeyEvent evt) {//GEN-FIRST:event_drugLimitFieldKeyReleased
        calcTotalLimit();
    }//GEN-LAST:event_drugLimitFieldKeyReleased

    private void visitLimitFieldKeyReleased(java.awt.event.KeyEvent evt) {//GEN-FIRST:event_visitLimitFieldKeyReleased
        calcTotalLimit();
    }//GEN-LAST:event_visitLimitFieldKeyReleased

    private void calcTotalLimit()
    {
        try
        {
            totalLimitLabel.setText(Double.parseDouble(drugLimitField.getText())+
                                    Double.parseDouble(visitLimitField.getText()) + "");
        }catch(Exception e)
        {
            totalLimitLabel.setText("");
        }
    }
    private void showProfitReport()
    {

        DefaultTableModel model = (DefaultTableModel) profitOverallTable.getModel();
        model.setRowCount(0);
        try {

            VisitOverallReport overRep = Reports.getVisitOverallReport(dateFrom,dateTo);
            ArrayList<ProfitReport> visRep = Reports.getProfitReport(dateFrom, dateTo);
            Label label;
            if(drugLimitField.getValue() != null)
            {
                visRep = limitProfitReport(visRep);
                overRep = fillVisitOverall(visRep);
            }
            WritableWorkbook wrk=null;
            WritableSheet sheet;

            model.addRow(new Object[]{"Лекарства", overRep.getDrugsTotalBuy(),
                                                   overRep.getDrugsTotalSell(),
                                                   overRep.getDrugProfit()});
            model.addRow(new Object[]{"Операции",  0.0,
                                                   overRep.getOperTotalSell(),
                                                   overRep.getOperProfit()});

            model.addRow(new Object[]{"Визиты",  0.0,
                                                   overRep.getVisitTotalPrice(),
                                                   overRep.getVisitTotalPrice()});

            model.addRow(new Object[]{"Другое",  0.0,
                                                   overRep.getVisitOtherTotalPrice(),
                                                   overRep.getVisitOtherTotalPrice()});
            model.addRow(new Object[]{"Всего",     overRep.getTotalBuy(),
                                                   overRep.getTotalSell(),
                                                   overRep.getTotalProfit()});

            profitOverallTable.setModel(model);

            model = (DefaultTableModel) profitReportTable.getModel();
            model.setRowCount(0);
            
            for(int i=0; i< visRep.size(); i++)
            {
                model.addRow(new Object[]{visRep.get(i).getDate(),
                                          visRep.get(i).getDay(),
                                          visRep.get(i).getVisitPrice(),
                                          visRep.get(i).getDrugsProfit(),
                                          visRep.get(i).getOperPrice(),
                                          visRep.get(i).getOtherPrice(),
                                          visRep.get(i).getOverall()
                });
                if(toExcel && wrk!=null)
                {
                    sheet = wrk.getSheet(0);
                    label = new Label(0, 2+i, visRep.get(i).getDate().toString());
                    sheet.addCell(label);
                    label = new Label(1, 2+i, visRep.get(i).getDay());
                    sheet.addCell(label);
                    label = new Label(2, 2+i, visRep.get(i).getVisitPrice()+"");
                    sheet.addCell(label);
                    label = new Label(3, 2+i, visRep.get(i).getDrugsProfit()+"");
                    sheet.addCell(label);
                    label = new Label(4, 2+i, visRep.get(i).getOperPrice()+"");
                    sheet.addCell(label);
                    label = new Label(5, 2+i, visRep.get(i).getOtherPrice()+"");
                    sheet.addCell(label);
                    label = new Label(6, 2+i, visRep.get(i).getOverall()+"");
                    sheet.addCell(label);
                }
            }
            if(toExcel)
            {
                wrk.write();
                wrk.close();
                Runtime.getRuntime().exec("explorer "+Dogy.REPORT_DIR);
            }
            profitReportTable.setModel(model);
        } catch (SQLException ex) {
            frame.SQLException(ex);
        } catch(Exception e)
        {
            frame.Exception(e);
        }

    }

    private void writeExcelVakcineReport(File file)
    {
        try {
            WritableSheet sheet;
            WritableWorkbook wrk = null;
            wrk = writeVakcineXLS(file);
            int nextVakcIndex = vakcTable.getColumnModel().getColumnIndex(Dictionary.NEXT_VAKC),
                vakcDateIndex = vakcTable.getColumnModel().getColumnIndex(Dictionary.VAKC_MADE),
                vakcInfoIndex = vakcTable.getColumnModel().getColumnIndex(Dictionary.VAKCINE),
                userInfoIndex = vakcTable.getColumnModel().getColumnIndex(Dictionary.CLIENT);

            for(int i=0; i< vakcTable.getRowCount(); i++)
            {
                    sheet = wrk.getSheet(0);
                    Label label = new Label(0, 2+i, vakcTable.getValueAt(i, nextVakcIndex).toString());
                    sheet.addCell(label);
                    label = new Label(1, 2+i, vakcTable.getValueAt(i, vakcDateIndex).toString());
                    sheet.addCell(label);
                    label = new Label(2, 2+i, vakcTable.getValueAt(i, vakcInfoIndex).toString());
                    sheet.addCell(label);
                    label = new Label(3, 2+i, vakcTable.getValueAt(i, userInfoIndex).toString());
                    sheet.addCell(label);
            }
            wrk.write();
            wrk.close();
            Runtime.getRuntime().exec("explorer "+Dogy.REPORT_DIR);
        } catch (Exception ex) {
            frame.Exception(ex);
        }

    }
    private void showVakcine()
    {
        vakcTable.setRowHeight(64);
        vakcTable.getColumnModel().getColumn(3).setCellRenderer(new TableTextAreaRenderer());
        DefaultTableModel model = (DefaultTableModel) vakcTable.getModel();
        
        model.setRowCount(0);
        try {
            ArrayList<VakcineReport> vakcRep = Reports.getVakcineReport();


            for(int i=0; i< vakcRep.size(); i++)
            {
                String userInfo = vakcRep.get(i).getUser().getSurname() + " (" +
                                  vakcRep.get(i).getUser().getStreet()+ ") "+
                                  vakcRep.get(i).getUser().getTel() + "\n"+
                                  vakcRep.get(i).getDog().toString() + " " +
                                  vakcRep.get(i).getDog().getAge() + " (Лет.месяцев)";
                String vakcInfo = vakcRep.get(i).getVakcineName();
                model.addRow(new Object[]{vakcRep.get(i).getNextVakcDate(),
                                          vakcRep.get(i).getVakcDate(),
                                          vakcInfo,
                                          userInfo});
            }

            vakcTable.setModel(model);

        } catch (SQLException ex) {
            frame.SQLException(ex);
        }
        catch(Exception e)
        {
            frame.Exception(e);
        }
    }

    // Variables declaration - do not modify//GEN-BEGIN:variables
    private javax.swing.JTable diagTable;
    private javax.swing.JFormattedTextField drugLimitField;
    private javax.swing.JFileChooser fileChooser;
    private javax.swing.JFormattedTextField fromDate;
    private javax.swing.JButton jButton1;
    private javax.swing.JButton jButton2;
    private javax.swing.JButton jButton3;
    private javax.swing.JCheckBox jCheckBox1;
    private javax.swing.JLabel jLabel1;
    private javax.swing.JLabel jLabel2;
    private javax.swing.JLabel jLabel3;
    private javax.swing.JLabel jLabel4;
    private javax.swing.JLabel jLabel5;
    private javax.swing.JLabel jLabel6;
    private javax.swing.JPanel jPanel1;
    private javax.swing.JPanel jPanel2;
    private javax.swing.JPanel jPanel3;
    private javax.swing.JPanel jPanel4;
    private javax.swing.JPanel jPanel5;
    private javax.swing.JScrollPane jScrollPane1;
    private javax.swing.JScrollPane jScrollPane2;
    private javax.swing.JScrollPane jScrollPane3;
    private javax.swing.JScrollPane jScrollPane4;
    private javax.swing.JScrollPane jScrollPane6;
    private javax.swing.JSeparator jSeparator1;
    private javax.swing.JDialog limitDialog;
    private javax.swing.JTable profitOverallTable;
    private javax.swing.JTable profitReportTable;
    private javax.swing.JTabbedPane reports;
    private javax.swing.JButton saveAsButton;
    private javax.swing.JButton showReport;
    private javax.swing.JFormattedTextField toDate;
    private javax.swing.JLabel totalLimitLabel;
    private javax.swing.JTable vakcTable;
    private javax.swing.JTextField visitLimitField;
    private javax.swing.JTable visitReportTable;
    private javax.swing.JPanel visitSettingsPanel;
    private javax.swing.JSplitPane visitSplitter;
    private javax.swing.JFormattedTextField visitVariance;
    private javax.swing.JCheckBox withPredv;
    // End of variables declaration//GEN-END:variables

    private WritableWorkbook writeVakcineXLS(File file) throws Exception
    {
                    
                    WritableWorkbook workbook = createWorkBook(file, "VakcineReport");

                    WritableSheet sheet = workbook.createSheet("Вакцинация", 0);
                    WritableCellFormat cf = new WritableCellFormat();
                    WritableFont times16font = new WritableFont(WritableFont.ARIAL, 13, WritableFont.BOLD);
                    cf.setFont(times16font);
                    Label label = new Label(0, 1, Dictionary.NEXT_VAKC);
                    label.setCellFormat(cf);
                    sheet.addCell(label);
                    label = new Label(1, 1, Dictionary.VAKC_MADE);
                    label.setCellFormat(cf);
                    sheet.addCell(label);
                    label = new Label(2, 1, Dictionary.VAKCINE);
                    label.setCellFormat(cf);
                    sheet.addCell(label);
                    label = new Label(3, 1, Dictionary.CLIENT);
                    label.setCellFormat(cf);
                    sheet.addCell(label);
                    CellView cv = sheet.getColumnView(3);
                    cv.setSize(70*256);
                    sheet.setColumnView(3, cv);
                    cv.setSize(25*256);
                    sheet.setColumnView(0, cv);
                    sheet.setColumnView(1, cv);
                    sheet.setColumnView(2, cv);
                    return workbook;
        
    }

    private WritableWorkbook writeProfitReportXLS(File file) throws Exception{

                    WritableWorkbook workbook = createWorkBook(file, "ProfitReport");

                    WritableSheet sheet = workbook.createSheet("Визиты", 0);
                    WritableCellFormat cf = new WritableCellFormat();
                    WritableFont times16font = new WritableFont(WritableFont.ARIAL, 12, WritableFont.BOLD);
                    cf.setFont(times16font);
                    Label label = new Label(0, 1, "Дата");
                    label.setCellFormat(cf);
                    sheet.addCell(label);
                    label = new Label(1, 1, "День недели");
                    label.setCellFormat(cf);
                    sheet.addCell(label);
                    label = new Label(2, 1, "Визит");
                    label.setCellFormat(cf);
                    sheet.addCell(label);
                    label = new Label(3, 1, "Лекарства");
                    label.setCellFormat(cf);
                    sheet.addCell(label);
                    label = new Label(4, 1, "Операции");
                    label.setCellFormat(cf);
                    sheet.addCell(label);
                    label = new Label(5, 1, "Другое");
                    label.setCellFormat(cf);
                    sheet.addCell(label);
                    label = new Label(6, 1, "Сумма");
                    label.setCellFormat(cf);
                    sheet.addCell(label);
                    CellView cv = sheet.getColumnView(1);
                    cv.setSize(40*256);
                    sheet.setColumnView(3, cv);
                    cv.setSize(20*256);

                    sheet.setColumnView(0, cv);
                    sheet.setColumnView(1, cv);
                    sheet.setColumnView(2, cv);
                    sheet.setColumnView(3, cv);
                    sheet.setColumnView(4, cv);
                    sheet.setColumnView(5, cv);
                    sheet.setColumnView(6, cv);
                    return workbook;
    }

    private WritableWorkbook writeVisitReportXLS(File file) throws Exception{
                    SimpleDateFormat sdf = new SimpleDateFormat("dd.MM.yyyy");
                    WritableWorkbook workbook = createWorkBook(file, "VisitReport");

                    WritableSheet sheet = workbook.createSheet("Визиты", 0);
                    sheet.setPageSetup(PageOrientation.LANDSCAPE, 0.6, 0.65);
                    SheetSettings sheetSettings = sheet.getSettings();
                    sheetSettings.setRightMargin(0.56);
                    sheetSettings.setPrintTitlesRow(1,1);
                    sheetSettings.setFitToPages(true);
                    
                    WritableCellFormat cf = new WritableCellFormat();
                    cf.setBorder(Border.BOTTOM, BorderLineStyle.THIN, Colour.BLACK);
                    //cf.setBorder(Border.LEFT, BorderLineStyle.HAIR, Colour.BLACK);
                    WritableFont times16font = new WritableFont(WritableFont.TAHOMA, 8, WritableFont.BOLD);
                    cf.setFont(times16font);
                    Label label = new Label(0, 1, "Kuupaev");
                    label.setCellFormat(cf);
                    sheet.addCell(label);
                    label = new Label(1, 1, "Pere-nimi");
                    label.setCellFormat(cf);
                    sheet.addCell(label);
                    label = new Label(2, 1, "Aadres");
                    label.setCellFormat(cf);
                    sheet.addCell(label);
                    label = new Label(3, 1, "Ravim");
                    label.setCellFormat(cf);
                    sheet.addCell(label);
                    label = new Label(4, 1, "Ravim KM");
                    label.setCellFormat(cf);
                    sheet.addCell(label);
                    label = new Label(5, 1, "Visit+Too");
                    label.setCellFormat(cf);
                    sheet.addCell(label);
                    label = new Label(6, 1, "Visit KM-"+Dogy.VAT_TAX+"%");
                    label.setCellFormat(cf);
                    sheet.addCell(label);
                    label = new Label(7, 1, "Kokku KM-TA");
                    label.setCellFormat(cf);
                    sheet.addCell(label);
                    label = new Label(8, 1, "Kokku KM");
                    label.setCellFormat(cf);
                    sheet.addCell(label);
                    label = new Label(9, 1, "Ravim Makstud");
                    label.setCellFormat(cf);
                    sheet.addCell(label);
                    label = new Label(10, 1, "Visit Makstud");
                    label.setCellFormat(cf);
                    sheet.addCell(label);
                    label = new Label(11, 1, "Kokku Makstud");
                    label.setCellFormat(cf);
                    sheet.addCell(label);
                    label = new Label(12, 1, "C");
                    label.setCellFormat(cf);
                    sheet.addCell(label);

                    CellView cv = sheet.getColumnView(1);
                    cv.setSize(10*256);

                    sheet.setColumnView(0, cv);
                    sheet.setColumnView(1, cv);
                    sheet.setColumnView(2, cv);
                    cv.setSize(8*256);
                    sheet.setColumnView(3, cv);
                    cv.setSize(10*256);
                    sheet.setColumnView(4, cv);
                    sheet.setColumnView(5, cv);
                    sheet.setColumnView(6, cv);
                    cv.setSize(10*256);
                    sheet.setColumnView(7, cv);
                    sheet.setColumnView(8, cv);
                    cv.setSize(12*256);
                    sheet.setColumnView(9, cv);
                    sheet.setColumnView(10, cv);
                    sheet.setColumnView(11, cv);
                    return workbook;
    }

    private WritableWorkbook createWorkBook(File file, String filename) throws IOException
    {
              SimpleDateFormat sdf = new SimpleDateFormat("dd.MM.yyyy");
                    WritableWorkbook workbook = null;
                    if(file == null)
                      workbook =
                            Workbook.createWorkbook(
                            new File(Dogy.REPORT_DIR +
                            "\\"+filename+"-"+sdf.format(Calendar.getInstance().getTime())+".xls"));
                    else
                    {
                      if(!file.getName().contains(".xls"))
                          file = new File(file.getAbsolutePath()+".xls");
                      workbook = Workbook.createWorkbook(file);
                    }
                    return workbook;
    }
}
